import os
import sys
import traceback
import time
import sqlite3
from bs4 import BeautifulSoup
import io

curPath = os.path.dirname(os.path.abspath(__file__))
sumCount = 0
sqlList = []
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='gb18030')


def SqliteEscape(keyWord):
    keyWord = keyWord.replace("'", "''")
    keyWord = keyWord.replace("%", "/%")

    return keyWord


def analysis(filePath):
    soup = BeautifulSoup(open(filePath, 'r', encoding='utf8'), 'lxml')
    divs = soup.find_all('div', class_='productbox3')
    for div in divs:
        aTitle = div.find('a', class_='producttitle')
        id = aTitle.get('href').replace('showpdf.aspx?id=', '')
        url = r'http://221.8.56.50:85/' + aTitle.get('href')
        title = ''.join(aTitle.stripped_strings).replace('Titles:', '')
        tags = div.find_all('div', class_='tags')
        author = ''
        ISBN = ''
        publisher = ''
        for tag in tags:
            line = ''.join(tag.stripped_strings)

            if line.startswith('Author:'):
                author = line.replace('Author:', '')
            elif line.startswith('ISBN:'):
                ISBN = line.replace('ISBN:', '').replace('/', ';')
            elif line.startswith('Press:'):
                publisher = line.replace('Press:', '')

        print('author:', author)
        print('isbn:', ISBN)
        print('press:', publisher)

        '''
        处理特殊字段
        '''
        provider = 'ebmbook_jlsti'
        rawid = id
        lngid = 'JLSTI_EBM_TS_' + rawid
        provider_id = provider + '@' + rawid
        provider_url = provider + '@' + url
        date_created = '19000000'
        Year = '1900'
        batch = time.strftime("%Y%m%d", time.localtime()) + '00'

        coverDir = os.path.join(curPath, 'cover', rawid[:4])
        coverPath = os.path.join(coverDir, rawid + '.jpg')
        print('coverPath:', coverPath)
        if os.path.exists(coverPath):
            coverPath = '/smartlib/' + provider + \
                '/' + rawid[:4] + '/' + rawid + '.jpg'
        else:
            coverPath = ''
        language = 'EN'
        country = 'CN'

        title = SqliteEscape(title)
        author = SqliteEscape(author)
        publisher = SqliteEscape(publisher)
        ISBN = SqliteEscape(ISBN)

        sql = '''INSERT INTO modify_title_info_zt (lngid, rawid, title,  identifier_pisbn, creator, publisher, date,  
        language, country, type, provider, provider_url, provider_id, medium, batch, date_created, cover) VALUES('{lngid}', '{rawid}', '{title}', 
        '{ISBN}', '{author}', '{publisher}', '{Year}', '{language}', '{country}', 
        '1', '{provider}', '{provider_url}', '{provider_id}', '2', '{batch}', '{date_created}', '{coverPath}')'''.format(lngid=lngid,rawid=rawid,title=title,
        ISBN=ISBN,author=author,publisher=publisher,Year=Year,language=language,country=country,provider=provider,provider_url=provider_url,
        provider_id=provider_id,batch=batch,date_created=date_created,coverPath=coverPath)

        sqlList.append(sql)

        if len(sqlList) > 1000:
            conn2db3()


def conn2db3():
    global sumCount, sqlList
    try:
        with sqlite3.connect('ebm.db3') as conn:
            print('open db3 successfully!')
            for sql in sqlList:
                conn.execute(sql)
                sumCount += 1
                print('成功插入数据库:{}条'.format(sumCount))
            conn.commit()

    except sqlite3.Error as e:
        print("******sqlite3 Error********:", sql)
        traceback.print_exc()
        sys.exit(-1)

    sqlList = []


def main():
    walkPath = os.path.join(curPath, 'detail')
    for path, dirNames, fileNames in os.walk(walkPath):
        for fileName in fileNames:
            filePath = os.path.join(path, fileName)
            print(filePath)
            analysis(filePath)


if __name__ == '__main__':
    main()
